package com.hy.lightning.ds.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.hy.lightning.ds.JdbcDataSource;
import com.hy.lightning.ds.SqlSession;
import com.hy.lightning.ds.jdbc.MapUtils.ResultBean;
import com.hy.lightning.ds.jdbc.datasources.C3p0DataSource;
import com.hy.lightning.ds.jdbc.datasources.JdbcConf;

public class BaseSqlSession implements SqlSession {

	private Logger logger = Logger.getLogger(getClass());

	private JdbcDataSource jdbcDataSource = new C3p0DataSource();

	private String dataSourceKey = "local";
	
	public BaseSqlSession() {
	}
	
	public BaseSqlSession(String dataSourceKey) {
		this.dataSourceKey = dataSourceKey;
	}
	
//	@Override
//	public boolean executeBatch(List<String> sqls, List<Object[]> paramsList) {
//		String sqlStrs = "";
//		for(String sql: sqls) {
//			sqlStrs += ";" + sql;
//		}
//		sqlStrs = sqlStrs.substring(1);
//		return executeBatch(sqlStrs, paramsList);
//	}
	
	private int executeBatch(String sql, List<Object[]> paramsList) {
		PreparedStatement preparedStatement = null;
		Connection conection = jdbcDataSource.getConnection(dataSourceKey);
		int[] relsult = null;
		try {
			conection.setAutoCommit(false);
			preparedStatement = getPreparedStatementBatch(conection, sql, paramsList);
			relsult = preparedStatement.executeBatch();
			int i = 0;
			for(int r : relsult) {
				if(r == 0) {
					conection.rollback();
					return 0;
				}
				i++;
			}
			conection.commit();
			conection.setAutoCommit(true);
			return i;
		} catch (SQLException e) {
			try {
				conection.rollback();
			} catch (SQLException e1) {}
			logger.error(e, e);
			return 0;
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (conection != null)
					conection.close();
			} catch (SQLException e) {

			}
		}
	}

	@Override
	public int update(String sql, Object... params) {
		PreparedStatement preparedStatement = null;
		int relsult = 0;
		Connection conection = jdbcDataSource.getConnection(dataSourceKey);
		try {
			preparedStatement = getPreparedStatement(conection, sql, params);
			relsult = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e, e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
				if (conection != null)
					conection.close();
			} catch (SQLException e) {

			}
		}
		return relsult;
	}
	
	
	@Override
	public List<Map<String, Object>> select(String sql, Object... params) {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Map<String, Object>> list = null;
		Connection conection = jdbcDataSource.getConnection(dataSourceKey);
		try {
			preparedStatement = getPreparedStatement(conection, sql, params);
			resultSet = preparedStatement.executeQuery();
			list = new ArrayList<>();
			ResultSetMetaData metaData = resultSet.getMetaData();
			while (resultSet.next()) {
				Map<String, Object> resultMap = new HashMap<>();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					String key = metaData.getColumnLabel(i + 1);
					Object value = resultSet.getObject(i + 1);
					resultMap.put(key, value);
				}
				list.add(resultMap);
			}
		} catch (SQLException e) {
			logger.error(e, e);
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
				if (conection != null)
					conection.close();
			} catch (SQLException e) {

			}
		}
		return list;
	}

	@Override
	public Map<String, Object> selectOne(String sql, Object... params) {
		if (!sql.toLowerCase().contains("limit")) {
			sql += " LIMIT 1";
		}
		List<Map<String, Object>> list = select(sql, params);
		if (list == null || list.size() == 0)
			return null;
		return list.get(0);
	}

	@Override
	public int insert(String tableName, Map<String, Object> map) {
		ResultBean resultBean = MapUtils.getInsertSqlByMap(tableName, map);
		return update(resultBean.getSql(), resultBean.getParams());
	}

	@Override
	public int insertBatch(String tableName, List<Map<String, Object>> list) {
		List<ResultBean> resultBeanList = MapUtils.getInsertBatchSqlByMaps(tableName, list);
		List<Object[]> params = new ArrayList<>();
		for(ResultBean eBean: resultBeanList) {
			params.add(eBean.params);
		}
		return executeBatch(resultBeanList.get(0).sql, params);
	}

	@Override
	public int update(String tableName, Map<String, Object> map) {
		ResultBean resultBean = MapUtils.getUpdateSqlByMap(tableName, map);
		return update(resultBean.getSql(), resultBean.getParams());
	}

	@Override
	public int updateBatch(String tableName, List<Map<String, Object>> list) {
		List<ResultBean> resultBeanList = MapUtils.getUpdateBatchSqlByMaps(tableName, list);
		List<Object[]> params = new ArrayList<>();
		for(ResultBean eBean: resultBeanList) {
			params.add(eBean.params);
		}
		return executeBatch(resultBeanList.get(0).sql, params);
	}

	@Override
	public int getCount(String sql, Object... params) {
		Map<String, Object> map = selectOne(sql, params);
		if (map == null)
			return 0;
		for (String key : map.keySet()) {
			if (map.get(key) != null)
				return Integer.valueOf(map.get(key).toString());
		}
		return 0;
	}
	
	@Override
	public int delete(String tableName, Map<String, Object> map) {
		ResultBean resultBean = MapUtils.getDeleteSqlByMap(tableName, map);
		return update(resultBean.getSql(), resultBean.getParams());
	}
	
	@Override
	public int deleteBatch(String tableName, List<Map<String, Object>> list) {
		List<ResultBean> resultBeanList = MapUtils.getDeleteBatchSqlByMaps(tableName, list);
		List<Object[]> params = new ArrayList<>();
		for(ResultBean eBean: resultBeanList) {
			params.add(eBean.params);
		}
		return executeBatch(resultBeanList.get(0).sql, params);
	}

	/**************************************** 私有方法 **************************************************************/

	private PreparedStatement getPreparedStatement(Connection connection, String sql, Object... params)
			throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		if(params == null || params.length == 0) return preparedStatement;
		for (int i = 0; i < params.length; i++) {
			Object object = params[i];
			preparedStatement.setObject(i + 1, object);
		}
		return preparedStatement;
	}
	
	private PreparedStatement getPreparedStatementBatch(Connection connection, String sql, List<Object[]> paramss)
			throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		for (Object[] params : paramss) {
			int i = 0;
			for (Object object: params) {
				preparedStatement.setObject(++i, object);
			}
			preparedStatement.addBatch();
		}
		return preparedStatement;
	}
	
	public static void main(String[] args) {
		JdbcConf jdbcConf = new JdbcConf("local","test.sharetasty.com", "tasty_0306", "root", "49BA59ABBE56E057");
		JdbcDataSource.initDataSource(jdbcConf);
		SqlSession session = new BaseSqlSession();
		String sql = "select id,title from Note limit 5";
		List<Map<String, Object>> list = session.select(sql);
		System.out.println(session.updateBatch("Note", list));
	}
}
